import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;

/**
 * v0.8 - v0.9 - menteni kellene dátumozott táblákba a számítások menetét, hogy
 * bármikor vissza lehesen követni a dolgokat. Itt csak azokat a rendeléseket
 * dolgozzuk fel, amik az utolsó history tánblánál újabbak. Továbbá a feldolgozó
 * szoftvert tele kell rakn imindenféle vészleállítókkal. Státuszok ........
 * 
 * @author osz-domosija
 */
// Hogyan lehet tesztelni az eredményt?
// - pl. összesítve a rendelt összeg és a kiosztott reward pontok 10%-os arány
// alatt vannak-e? Ezeket az arányokat ki is kellene irni a végén.
// - találomra kiválasztunk pár embert, és tőle kiindulva végignézzük az ő
// embereinek rendelését és az onnan nyert reward pontokat.
// + Az utólag problémázó (rendelését visszamodó) ügyfeleket mindig meg kell
// vizsgálni, hogy a aktív-e vagy sem, mert ay utolsó rendelése alapján
// feltételeyyük, hogy akít lesz az elszámoláskor. Vagy hogyanb is van ez????
public class RewardProcessor {

	public static final String CODE = "code";

	public static final String VALUE = "value";

	public static final String REWARD = "reward";

	public static final String SUB_TOTAL = "sub_total";

	public static final int VALIDATIION_DAYS = 180;

	public static final String DB_HOST = "localhost";// 91.227.139.141,
														// localhost

	public static final int DB_PORT = 3306;

	public static final String DB_USER = "vincell_web";// vincell_web, root;

	public static final String DB_PASSWORD = "freebex08";// freebex08, domike

	public static String TABLE_REWARDS = null;

	public static final int PROCESSING_ORDER_STATUS_ID = 3;

	public static final BigDecimal SELF_PERCENTAGE = new BigDecimal("0.04");

	public static final BigDecimal PARENT_PERCENTAGE = new BigDecimal("0.03");

	public static final BigDecimal GRAND_PARENT_PERCENTAGE = new BigDecimal(
			"0.02");

	public static final BigDecimal GRAND_GRAND_PARENT_PERCENTAGE = new BigDecimal(
			"0.01");

	public static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat(
			"yyyy.mm.dd");

	public Date processDate = null;

	public static boolean testMode = true;

	/**
	 * @param args
	 */
	public static void main(final String[] args) {
		if (args.length > 0) {
			if (args[0].trim().equals("live")) {
				testMode = false;
			} else if (args[0].trim().equals("test")) {
				testMode = true;
			} else {
				System.out.println("Empty arguments!");
				System.exit(0);
			}
		}

		if (testMode) {
			TABLE_REWARDS = "tmp_rewards";
		} else {
			TABLE_REWARDS = "processed_rewards";
		}

		new RewardProcessor().start();
	}

	private void start() {
		try {
			final Connection connection = getConnection();
			createAndLockTempTables(connection);
			readOrders(connection);

			if (testMode) {
				new TestRewards().start(connection);

			} else {
				// szummázás + státuszok átállítása?? + reward pontok
				// customerhez
				// adása
				sumRewards(connection);
				sumUserRewardsInfo(connection);
			}

		} catch (final Exception e) {
			System.out.println("Error: " + e.getMessage());
			e.printStackTrace();
		}
	}

	private Connection getConnection() throws SQLException {
		return DriverManager.getConnection("jdbc:mysql://" + DB_HOST + ":"
				+ DB_PORT
				+ "/vincell_web?useUnicode=yes&characterEncoding=UTF-8&user="
				+ DB_USER + "&password=" + DB_PASSWORD);
	}

	private void createAndLockTempTables(final Connection connection)
			throws SQLException {
		if (testMode) {
			connection.createStatement().execute(
					"drop table IF EXISTS " + TABLE_REWARDS);

			connection
					.createStatement()
					.execute(
							"create table "
									+ TABLE_REWARDS
									+ " (customer_id float, order_id float, points int, process_date datetime, percentage double, distance int, from_customer float, process_state int)");
		}
	}

	// az egyes rendelések feldolgozása külön-külön tranzakciókban kellene
	// lenniük.
	// az eredményeket temp táblába kellene rakni, és csak a sikeres befejezés
	// után átnevezni őket a dátumozott history táblákká.
	private void readOrders(final Connection connection) throws Exception {
		processDate = new Date();

		final PreparedStatement orderStatement = connection
				.prepareStatement("select order_id, customer_id, date_added from oc_order where order_status_id = ?");
		orderStatement.setInt(1, PROCESSING_ORDER_STATUS_ID);

		final ResultSet orderResultSet = orderStatement.executeQuery();
		while (orderResultSet.next()) {
			final float orderId = orderResultSet.getFloat("order_id");
			final float customerId = orderResultSet.getFloat("customer_id");
			final Timestamp dateAdded = orderResultSet
					.getTimestamp("date_added");

			//TODO a
			// 3-as státuszúaknak nem számolhatjuk el. csak.
			System.out.println("orderId: " + orderId + "\t\tcustomerId: "
					+ customerId);
			processOrder(connection, orderId, customerId, dateAdded);			
		}
	}

	// a rendelés dátumakor volt-e aktív?
	private boolean checkCustomer(final Connection connection,
			final float customerId, final Timestamp examinationDate)
			throws SQLException {
		boolean valid = false;

		final PreparedStatement customerStatement = connection
				.prepareStatement("select customer_id from oc_customer where customer_id = ? and reward_state_id < 3");
		customerStatement.setFloat(1, customerId);

		final ResultSet customerResultSet = customerStatement.executeQuery();

		if (customerResultSet.next()) {
			final Calendar calendar = GregorianCalendar.getInstance();
			calendar.setTime(examinationDate);
			calendar.add(Calendar.YEAR, -1);

			final PreparedStatement stateStatement = connection
					.prepareStatement("select customer_id from oc_customer_reward_state_history where customer_id = ? and state_id = 1 and modify_date between ? and ?");
			stateStatement.setFloat(1, customerId);
			stateStatement.setTimestamp(2, new Timestamp(calendar.getTime()
					.getTime()));
			stateStatement.setTimestamp(3, examinationDate);

			final ResultSet stateResultSet = stateStatement.executeQuery();
			valid = stateResultSet.next();
		}

		return valid;
	}

	private void processOrder(final Connection connection, final float orderId,
			final float customerId, final Timestamp dateAdded) throws Exception {
		// fizetett összeg
		final PreparedStatement orderStatement = connection
				.prepareStatement("select * from oc_order_total where order_id = ? and (code = 'sub_total' or code = 'reward')");
		orderStatement.setFloat(1, orderId);

		BigDecimal orderSubTotal = BigDecimal.ZERO;
		BigDecimal rewardTotal = BigDecimal.ZERO;
		final ResultSet orderResultSet = orderStatement.executeQuery();
		while (orderResultSet.next()) {
			final String code = orderResultSet.getString(CODE);
			if (code.equals(SUB_TOTAL)) {
				orderSubTotal = orderResultSet.getBigDecimal(VALUE);
			} else if (code.equals(REWARD)) {
				rewardTotal = orderResultSet.getBigDecimal(VALUE);
			}
		}

		BigDecimal payedProductTotal = orderSubTotal.add(rewardTotal);

		System.out.println("\ttotal: " + payedProductTotal);
		if (orderSubTotal.compareTo(BigDecimal.ZERO) == 0) {
			throw new Exception("0 rendelesi osszeg. order_id: " + orderId);
		}

		// szülők lekérdezése
		final Float parentCustomerId = getParentOf(connection, customerId,
				dateAdded);
		final Float grandParentCustomerId = getParentOf(connection,
				parentCustomerId, dateAdded);
		final Float grandGrandParentCustomerId = getParentOf(connection,
				grandParentCustomerId, dateAdded);

		// self
		// mikor is jár nekem a pont???????????????????????????????? Mi a
		// státuszom???
		insertPreReward(connection, customerId, customerId, orderId,
				payedProductTotal, SELF_PERCENTAGE, 0, dateAdded);
		// parent - mikor is jár nekem a szülőnek
		// pont???????????????????????????????? Szerintem itt számolni kell,
		// aztán majd a szülős státuszától
		// függően fog ez menni.
		insertPreReward(connection, customerId, parentCustomerId, orderId,
				payedProductTotal, PARENT_PERCENTAGE, 1, dateAdded);
		insertPreReward(connection, customerId, grandParentCustomerId, orderId,
				payedProductTotal, GRAND_PARENT_PERCENTAGE, 2, dateAdded);
		insertPreReward(connection, customerId, grandGrandParentCustomerId,
				orderId, payedProductTotal, GRAND_GRAND_PARENT_PERCENTAGE, 3, dateAdded);
	}

	private Float getParentOf(final Connection connection,
			final Float customerId, final Timestamp dateAdded)
			throws SQLException {
		Float parentCustomerId = null;

		if (customerId != null && customerId != 0) {
			final PreparedStatement parentStatement = connection
					.prepareStatement("select parent_customer_id from oc_customer where customer_id = ?");
			parentStatement.setFloat(1, customerId);

			final ResultSet parentResultSet = parentStatement.executeQuery();
			if (parentResultSet.next()) {
				if (!parentResultSet.wasNull()) {
					final Float _parent = parentResultSet
							.getFloat("parent_customer_id");
					//if (checkCustomer(connection, _parent, dateAdded)) {
						parentCustomerId = _parent;
					//}
				}
			}
		}

		return parentCustomerId;
	}

	private void insertPreReward(final Connection connection,
			final Float fromCustomerId, final Float customerId,
			final float orderId, final BigDecimal orderTotal,
			final BigDecimal percent, final int distance, final Timestamp dateAdded) throws Exception {
		if (customerId != null && customerId != 0 && orderTotal != null
				&& orderTotal.compareTo(BigDecimal.ZERO) != 0) {
			
			if (checkCustomer(connection, customerId, dateAdded)) {
				final int reward = orderTotal.multiply(percent).intValue();
	
				final PreparedStatement rewardStatement = connection
						.prepareStatement("insert into "
								+ TABLE_REWARDS
								+ " set customer_id = ?, order_id = ?, points = ?, process_date = ?, percentage = ?, distance = ?, from_customer = ?, process_state = ?");
				rewardStatement.setFloat(1, customerId);
				rewardStatement.setFloat(2, orderId);
				rewardStatement.setInt(3, reward);
				rewardStatement.setTimestamp(4,
						new Timestamp(processDate.getTime()));
				rewardStatement.setBigDecimal(5, percent);
				rewardStatement.setInt(6, distance);
				rewardStatement.setFloat(7, fromCustomerId);
				rewardStatement.setFloat(8, 0);
	
				final int insertRowNum = rewardStatement.executeUpdate();
				if (insertRowNum == 0) {
					throw new Exception("0 beszurt sor a " + TABLE_REWARDS
							+ " táblába. order_id: " + orderId);
				}
			}
		}
	}

	private void sumRewards(final Connection connection) throws Exception {
		final PreparedStatement custRewardStatement = connection
				.prepareStatement("insert into oc_customer_reward set customer_id = ?, points = ?, date_added = ?, validation_date = ?, description = ?");

		final PreparedStatement tmpRewardStatement = connection
				.prepareStatement("select customer_id, SUM(points) as sum_points, process_date FROM "
						+ TABLE_REWARDS
						+ " WHERE process_date = ? and process_state = 0 GROUP BY customer_id");
		tmpRewardStatement
				.setTimestamp(1, new Timestamp(processDate.getTime()));

		final ResultSet tmpResultSet = tmpRewardStatement.executeQuery();
		while (tmpResultSet.next()) {
			final float customerId = tmpResultSet.getFloat("customer_id");
			final BigDecimal rewards = tmpResultSet.getBigDecimal("sum_points");
			final Timestamp addedAt = tmpResultSet.getTimestamp("process_date");

			if (rewards.compareTo(BigDecimal.ZERO) > 0) {
				// insert
				custRewardStatement.setFloat(1, customerId);
				custRewardStatement.setInt(2, rewards.intValue());
				custRewardStatement.setTimestamp(3, addedAt);
				final Calendar calendar = GregorianCalendar.getInstance();
				calendar.setTime(addedAt);
				calendar.add(Calendar.DAY_OF_YEAR, VALIDATIION_DAYS);
				custRewardStatement.setTimestamp(4, new Timestamp(calendar
						.getTime().getTime()));
				custRewardStatement.setString(5, "Ajándékpont elszámolás - "
						+ DATE_FORMAT.format(addedAt));

				final int insertRowNum = custRewardStatement.executeUpdate();
				if (insertRowNum == 0) {
					System.out
							.println("0 beszurt sor a oc_customer_reward táblába. customer_id: "
									+ customerId);
				} else {
					// email
					final PreparedStatement customerStatement = connection
							.prepareStatement("select email, firstname, lastname from oc_customer where customer_id = ?");
					customerStatement.setFloat(1, customerId);

					final ResultSet customerResultSet = customerStatement
							.executeQuery();

					if (customerResultSet.next()) {
						final String email = customerResultSet
								.getString("email");
						final String firstname = customerResultSet
								.getString("firstname");
						final String lastname = customerResultSet
								.getString("lastname");

						String name = email;
						if (firstname != null && !firstname.trim().isEmpty()) {
							name = firstname;

						} else if (lastname != null
								&& !lastname.trim().isEmpty()) {
							name = lastname;
						}

						if (email != null && !email.trim().isEmpty()) {
							SendEmail.send(email, name);
						} else {
							System.out
									.println("Empty email address. customer_id: "
											+ customerId);
						}
					}
				}
			}
		}

		// state-k feldolgozottra állítása
		final PreparedStatement updateStatement = connection
				.prepareStatement("update " + TABLE_REWARDS
						+ " set process_state = 1 where process_date = ?");
		updateStatement.setTimestamp(1, new Timestamp(processDate.getTime()));
		updateStatement.executeUpdate();

		// update order states insert state_history
		final PreparedStatement updateOrderStatement = connection
				.prepareStatement("update oc_order set order_status_id = 5 where order_id = ?");

		final PreparedStatement insertHistoryStatement = connection
				.prepareStatement("insert into oc_order_history set order_id = ?, order_status_id = 5, comment = 'Elszámolva', date_added = ?");

		final PreparedStatement selectRewardStatement = connection
				.prepareStatement("select distinct order_id FROM "
						+ TABLE_REWARDS
						+ " WHERE process_date = ? and process_state = 1");
		selectRewardStatement.setTimestamp(1,
				new Timestamp(processDate.getTime()));

		final ResultSet selectResultSet = selectRewardStatement.executeQuery();
		while (selectResultSet.next()) {
			final float orderId = selectResultSet.getFloat("order_id");

			updateOrderStatement.setFloat(1, orderId);
			updateOrderStatement.executeUpdate();

			// insert history
			insertHistoryStatement.setFloat(1, orderId);
			insertHistoryStatement.setTimestamp(2,
					new Timestamp(System.currentTimeMillis()));
			insertHistoryStatement.executeUpdate();
		}
	}

	// TODO process_state = 1
	private void sumUserRewardsInfo(final Connection connection)
			throws Exception {
		final PreparedStatement userRewardStatement = connection
				.prepareStatement("insert into user_rewards_info set customer_id = ?, distance = ?, process_date = ?, level_0_value = ?, level_0_customer_id = ?, level_1_value = ?, level_1_customer_id = ?, level_2_value = ?, level_2_customer_id = ?, level_3_value = ?, level_3_customer_id = ?");

		final PreparedStatement tmpRewardStatement = connection
				.prepareStatement("select customer_id, distance, Sum(points) AS sum_points, process_date, from_customer FROM "
						+ TABLE_REWARDS
						+ " WHERE process_date = ? and process_state = 1 GROUP BY customer_id, from_customer");
		tmpRewardStatement
				.setTimestamp(1, new Timestamp(processDate.getTime()));

		final ResultSet tmpResultSet = tmpRewardStatement.executeQuery();
		while (tmpResultSet.next()) {
			final float customerId = tmpResultSet.getFloat("customer_id");
			final float fromCustomerId = tmpResultSet.getFloat("from_customer");
			final int distance = tmpResultSet.getInt("distance");
			final BigDecimal rewards = tmpResultSet.getBigDecimal("sum_points");
			final Timestamp addedAt = tmpResultSet.getTimestamp("process_date");

			userRewardStatement.setFloat(1, customerId);
			userRewardStatement.setInt(2, distance);
			userRewardStatement.setTimestamp(3, addedAt);

			userRewardStatement.setNull(4, Types.INTEGER);
			userRewardStatement.setNull(5, Types.FLOAT);
			userRewardStatement.setNull(6, Types.INTEGER);
			userRewardStatement.setNull(7, Types.FLOAT);
			userRewardStatement.setNull(8, Types.INTEGER);
			userRewardStatement.setNull(9, Types.FLOAT);
			userRewardStatement.setNull(10, Types.INTEGER);
			userRewardStatement.setNull(11, Types.FLOAT);

			if (distance == 0) {
				userRewardStatement.setInt(4, rewards.intValue());
				userRewardStatement.setFloat(5, fromCustomerId);

			} else if (distance == 1) {
				userRewardStatement.setInt(6, rewards.intValue());
				userRewardStatement.setFloat(7, fromCustomerId);

			} else if (distance == 2) {
				userRewardStatement.setInt(8, rewards.intValue());
				userRewardStatement.setFloat(9, fromCustomerId);

			} else if (distance == 3) {
				userRewardStatement.setInt(10, rewards.intValue());
				userRewardStatement.setFloat(11, fromCustomerId);
			}

			userRewardStatement.executeUpdate();
		}
	}
}
